capture log close
clear
capture program drop _all
set more off
timer clear 1
timer on 1
set seed 123

**************** 
***  PATHS   ***  
****************
*BCCR: 1 if at Central Bank
global BCCR=1
if $BCCR == 1 {
	global path "Y:/Projects/MNC_Suppliers/Suppliers_Alfaro-Urena_Manelici_Vasquez"
}
if $BCCR == 0 {
	global path "~/Documents/GitHub/Suppliers_Alfaro-Urena_Manelici_Vasquez"
}
cd $path 

*log file
local logfile "${path0}/log_files/4-appendix_a"
set linesize 255
log using `logfile', replace
set linesize 255

/*
RESULTS IN APPENDIX A OF THE PAPER
*/

********************** 
***  INPUT FILES   ***  
**********************
global analysis_data ""processed_data/analysis_data.dta""
global transactions ""raw_data/trans_clean_corresp.dta""
global mnc_sample ""processed_data/MNC_sample.dta""
global mnc_countries ""raw_data/mnc_countries_sample.dta""
global revec_group ""raw_data/revec_group.dta""
global foreign ""raw_data/all_foreign.dta""
global SL_info ""raw_data/SL_info.dta""

********************************************************************************
* main program: outline of the full code
********************************************************************************
prog main

*Table A1 
*Coverage of Data After Minimal Size Restrictions
		samplecoverage
		
*Table A2
*Descriptive Statistics After Minimal Size Restrictions
		descriptive

*Tables A3 and A4: 
*A3: Number of Cases, Firm-to-firm Transaction Data
*A4: Value of Transactions, Firm-to-firm Transaction Data
		desc_trans

*Table A5
*Descriptive Statistics by Firm Ownership
		mncdescriptive 
		
*Table A6
*MNC Sample Coverage
		mnc_share
		
*Table A7
*Country of Global Ultimate Ownership
		mnc_country
		
*Table A8
*Sectoral and Country Composition for all MNCs and procomer buyers
		descevent_sectcomp_proco

*Tables A9, A10 and Figures A2, A3
**Alternative Empirical Strategies: ``Productive Linkages" 
		procomer
		
*Figure A1. This figure is a picture from the survey form

end

********************************************************************************
*Table A1 
*Coverage of Data After Minimal Size Restrictions
********************************************************************************
prog samplecoverage
display "table A1: Coverage of Data After Minimal Size Restrictions"
global Vars "sales workers wage_bill exports_ imports_ value_added input_costs total_net_assets"

*balance sheet data (all domestic firms in revec)
quiet{
* same chunk as in 1-event_data_creation.do
		balance_sheet
	
*rest of the code
drop if foreign_list == 1	
egen IDs=group(ID in_main_sample)
distinct IDs 
gen firms=`r(ndistinct)'
*totals
keep $Vars in_main_sample firms
foreach v of global Vars {
egen tot_`v'=total(`v')
}
*totals in sample 
keep if in_main_sample==1
foreach v of global Vars {
egen tot_sample_`v'=total(`v')
}
*shares 
foreach v of global Vars {
gen sh_`v'=100*tot_sample_`v'/tot_`v'
}
keep if _n==1
keep sh* firms
gen sample="Raw Data (with non-missing sector, sales and empl.)"
tempfile coverage1
save `coverage1', replace
}

*balance sheet data (all firms in revec minus gov)
quiet{
* same chunk as in 1-event_data_creation.do
		balance_sheet

* NO GOVERNMENT, NO NGO
drop if inolucrogrupo==1 | ipubgrupo==1	

*rest of the code
drop if foreign_list == 1
egen IDs=group(ID in_main_sample)
distinct IDs 
gen firms=`r(ndistinct)'
*totals
keep $Vars in_main_sample firms
foreach v of global Vars {
egen tot_`v'=total(`v')
}
*totals in sample 
keep if in_main_sample==1
foreach v of global Vars {
egen tot_sample_`v'=total(`v')
}
*shares 
foreach v of global Vars {
gen sh_`v'=100*tot_sample_`v'/tot_`v'
}
keep if _n==1
keep sh* firms
gen sample="+ Excluding State-Owned Enterprises"
tempfile coverage2
save `coverage2', replace
}

*balance sheet data (all firms in revec minus gov minus financial)
quiet{
* same chunk as in 1-event_data_creation.do
		balance_sheet
		
* NO GOVERNMENT, NO NGO
drop if inolucrogrupo==1 | ipubgrupo==1
* NO financial  
replace sector_institucional=substr(sector_institucional, 1, 3)
keep if sector_ins=="S11" | sector_ins=="S14"
drop if sector_g==17 | sector_g==13

*rest of the code
drop if foreign_list == 1	
egen IDs=group(ID in_main_sample)
distinct IDs 
gen firms=`r(ndistinct)'
*totals
keep $Vars in_main_sample firms
foreach v of global Vars {
egen tot_`v'=total(`v')
}
*totals in sample 
keep if in_main_sample==1
foreach v of global Vars {
egen tot_sample_`v'=total(`v')
}
*shares 
foreach v of global Vars {
gen sh_`v'=100*tot_sample_`v'/tot_`v'
}
keep if _n==1
keep sh* firms
gen sample="+ Excluding Finance and Education"
tempfile coverage3
save `coverage3', replace
}

*balance sheet data (all firms in revec minus gov minus financial minus households)
quiet{
* same chunk as in 1-event_data_creation.do
		balance_sheet
		
* NO GOVERNMENT, NO NGO
drop if inolucrogrupo==1 | ipubgrupo==1
* NO financial  
replace sector_institucional=substr(sector_institucional, 1, 3)
keep if sector_ins=="S11" 
drop if sector_g==17 | sector_g==13

*rest of the code
drop if foreign_list == 1	
egen IDs=group(ID in_main_sample)
distinct IDs 
gen firms=`r(ndistinct)'
*totals
keep $Vars in_main_sample firms
foreach v of global Vars {
egen tot_`v'=total(`v')
}
*totals in sample 
keep if in_main_sample==1
foreach v of global Vars {
egen tot_sample_`v'=total(`v')
}
*shares 
foreach v of global Vars {
gen sh_`v'=100*tot_sample_`v'/tot_`v'
}
keep if _n==1
keep sh* firms
gen sample="+ Excluding Households"
tempfile coverage4
save `coverage4', replace
}

* after sector restrictions 		
quiet{
*data preparation
	data_prep
	
*rest of the code
drop if foreign_list == 1
egen IDs=group(ID in_main_sample)
distinct IDs 
gen firms=`r(ndistinct)'	
*totals
keep $Vars in_main_sample firms 
foreach v of global Vars {
egen tot_`v'=total(`v')
}
*totals in sample 
keep if in_main_sample==1

foreach v of global Vars {
egen tot_sample_`v'=total(`v')
}
*shares 
foreach v of global Vars {
gen sh_`v'=100*tot_sample_`v'/tot_`v'
}
keep if _n==1
keep sh* firms
gen sample="+ Excluding Construction"
tempfile coverage5
save `coverage5', replace
}
quiet{
clear
forvalue p=1/5{
append using `coverage`p''
}
ds sh*
foreach p in `r(varlist)'{
	replace `p'=round(`p'*100)/100
}
} 	
rename (sh_sales sh_workers sh_wage_bill sh_exports_ sh_imports_ sh_value_added sh_input_costs sh_total_net_assets firms) (Sales Empl Wage_Bill Exports Imports VA Inputs Net_Assets Firms)
list sample Sales Empl Wage_Bill Exports Imports VA Inputs Net_Assets Firms
dataout, save(${path0}/results/1-appendix_a/Supplementary_Table_A1.tex) replace tex  dec(1)
end 

********************************************************************************
*Table A2
*Descriptive Statistics After Minimal Size Restrictions
********************************************************************************
prog descriptive
display "table A2: Descriptive Statistics After Minimal Size Restrictions"
quiet{
*data preparation
	data_prep

*rest of the code
drop if foreign_list == 1

global vars "sales workers wage_bill exports_ imports_ value_added input_costs total_net_assets"
collapse (mean) $vars , by (ID in_main_sample)
}

display "all firms in the non- financial market economy (upper panel) and for all firms kept in our sample of analysis (lower panel)."

label variable sales "Total Sales"
label variable workers "Number of Workers"
label variable wage_bill "Wage Bill"
label variable exports_ "Exports"
label variable imports_ "Imports"
label variable value_added "Value Added"
label variable input_costs "Input Costs"
label variable total_net_assets "Total Net Assets"
 
estpost tabstat $vars , stat(n mean sd median) columns(statistics)
esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A2.tex, ///
replace label cells("count(label(# Firms) fmt(0)) mean(label(Mean) fmt(1)) sd(label(S.D.) fmt(1)) p50(label(Median) fmt(1) )") ///
refcat( "Non-financial market economy \medskip", nolabel) ///
noeqlines noobs nonumber f booktabs ///
note("all firms in the non- financial market economy (upper panel) and for all firms kept in our sample of analysis (lower panel).")

*only in sample
estpost tabstat $vars if in_main_sample==1, stat(n mean sd median) columns(statistics)
esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A2.tex, ///
append label cells("count(fmt(0)) mean(fmt(1)) sd(fmt(1)) p50(fmt(1) )") ///
refcat( "Sample \medskip", nolabel) collabels(none) noobs ///
nonumber f plain booktabs ///
note("all firms in the non- financial market economy (upper panel) and for all firms kept in our sample of analysis (lower panel).")

end

********************************************************************************
*Tables A3 and A4: 
*A3: Number of Cases, Firm-to-firm Transaction Data
*A4: Value of Transactions, Firm-to-firm Transaction Data
********************************************************************************
prog desc_trans
if $BCCR == 1{
** Importing datasets
**
foreach l in 2008 2012 2015 {
clear
import delimited "Y:\Datasets\Raw_Data\D151\D151-ANI\0. Base final D151 `l'.csv", encoding(UTF-8) 
destring monto_def, replace force 
destring operacion, replace force 
save temp/temp_d151_`l'.dta, replace 
}
clear
foreach l in 2008 2012 2015 {
append using temp/temp_d151_`l'.dta , force 
}
foreach l in 2008 2012 2015 {
capture erase temp/temp_d151_`l'.dta  
}
** Table A3
**
capture destring caso_final, replace force
keep if caso_final != 4
replace caso_final = 4 if (caso_final > 2 & caso_final <3)

label define casos_ 0 "Unsolved" 1 "Data in pairs" 2 "No partner and accepted" 3 "Duplicate" 4 "No partner and rejected" 5 "Excluded"
label values caso_final casos_
label variable caso_final "Type of case"

tabout caso_final year using ${path0}/results/1-appendix_a/Supplementary_Table_A3.tex, ///
cells(freq col) clab("Count" "%") bt style(tex) total(Total Total_three_years) replace

** Table A4 
**
rename operacion Value_
*PLEASE CONVERT TO (millions of) USD AND DEFLATE THE VARIABLE Value_
keep caso_final year Value_
collapse (sum) Value_, by(caso_final year)
reshape wide Value_, i(caso_final) j(year)

**  CONVERTING TO REAL VALUES 
* money in millions
local money_col "Value"
forvalue year =2005/ $last_year_revec {
foreach vari of local money_col{
capture replace `vari'_`year'= `vari'_`year'/1000000/${tc_`year'} 
capture format `vari'_`year' %16.0g
}
}

egen total_2008 = sum(Value_2008)
gen perc_2008 = 100*Value_2008/total_2008
egen total_2012 = sum(Value_2012)
gen perc_2012 = 100*Value_2012/total_2012
egen total_2015 = sum(Value_2015)
gen perc_2015 = 100*Value_2015/total_2015

keep caso_final Value_2008 perc_2008 Value_2012 perc_2012 Value_2015 perc_2015
order caso_final Value_2008 perc_2008 Value_2012 perc_2012 Value_2015 perc_2015

tabout caso_final using ${path0}/results/1-appendix_a/Supplementary_Table_A4.tex, ///
sum cells(sum Value_2008 sum perc_2008 sum Value_2012 sum perc_2012 sum Value_2015 sum perc_2015) bt style(tex) replace
}
end
********************************************************************************
*Table A5
*Descriptive Statistics by Firm Ownership 
********************************************************************************
prog mncdescriptive
display "table A5: Descriptive Statistics by Firm Ownership"
quiet{
*data preparation
	data_prep

*rest of the code
*merging domestic part of group
merge m:1 ID using $foreign
gen all_foreign=(_m==3)
drop if _m==2
drop _m
*merging MNCs 
merge m:1 ID using $mnc_sample
gen mncs=(_m==3)
drop _m 

global vars "sales workers wage_bill exports_ imports_ value_added input_costs total_net_assets entered_bef_2005"
collapse (mean) $vars , by (ID in_main_sample all_foreign mncs)
}
label variable sales "Total Sales"
label variable workers "Number of Workers"
label variable wage_bill "Wage Bill"
label variable exports_ "Exports"
label variable imports_ "Imports"
label variable value_added "Value Added"
label variable input_costs "Input Costs"
label variable total_net_assets "Total Net Assets"
label variable entered_bef_2005 "Firms Entering Pre-2005"

display "Fully domestic, foreign - large MNCs, large MNCs"

display "Fully domestic"
estpost tabstat $vars if all_foreign!=1, ///
 stat(n mean sd median) columns(statistics)
esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A5.tex, ///
replace label cells("count(label(Firms) fmt(0)) mean(label(Mean) fmt(1)) sd(label(S.D.) fmt(1)) p50(label(Median) fmt(1) )") ///
refcat( "Non-financial market economy \medskip", nolabel) ///
noeqlines noobs nonumber f booktabs ///
note("Fully domestic")

display "Foreign firms excluding large MNCs"
estpost tabstat $vars if all_foreign==1 & mncs!=1 ///
, stat(n mean sd median) columns(statistics)
esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A5.tex, ///
append label cells("count(fmt(0)) mean(fmt(1)) sd(fmt(1)) p50(fmt(1) )") ///
refcat( "Sample \medskip", nolabel) collabels(none) noobs ///
nonumber f plain booktabs ///
note("Firms with partial foreign ownership, excluding large MNCs")

display "large MNCs"
estpost tabstat $vars if mncs==1 ///
, stat(n mean sd median) columns(statistics)
esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A5.tex, ///
append label cells("count(fmt(0)) mean(fmt(1)) sd(fmt(1)) p50(fmt(1) )") ///
refcat( "Sample \medskip", nolabel) collabels(none) noobs ///
nonumber f plain booktabs ///
note("(Large) MNCs")

end 
********************************************************************************
*Table A6
*MNC Sample Coverage
********************************************************************************
prog mnc_share
display "table A6: MNC Sample Coverage"
quiet{
*data preparation
	data_prep

*rest of the code
*merging domestic part of group
merge m:1 ID using $foreign
gen all_foreign=(_m==3)
drop if _m==2
drop _m
*merging MNCs 
merge m:1 ID using $mnc_sample
gen mncs=(_m==3)
drop _m 

global vars "sales workers wage_bill exports_ imports_ value_added input_costs total_net_assets"

keep if all_foreign==1
*totals
keep $vars all_foreign mncs 
foreach v of global vars {
egen tot_`v'=total(`v')
}
*totals large MNCs
keep if mncs==1
foreach v of global vars {
egen tot_sample_`v'=total(`v')
}
*shares 
foreach v of global vars {
gen sh_`v'=100*tot_sample_`v'/tot_`v'
}
keep if _n==1
keep sh*
}
rename (sh_sales sh_workers sh_wage_bill sh_exports_ sh_imports_ sh_value_added sh_input_costs sh_total_net_assets) (Total_Sales Number_Workers Wage_Bill Exports Imports Value_Added Input_Costs Total_Net_Assets)
list
dataout, save(${path0}/results/1-appendix_a/Supplementary_Table_A6.tex) replace tex  dec(1)
end 

********************************************************************************
*Table A7
*Country of Global Ultimate Ownership
********************************************************************************
prog mnc_country
display "Country of Global Ultimate Ownership for the MNCs Triggering the Event"
quiet{

*mncs in main sample 
use $mnc_sample , clear
keep ID 
rename ID buyer
*merging with countries 
merge 1:1 buyer using $mnc_countries
keep if _m==3
replace country="US" if country=="PR"
capture rename country Country
*table 
tabout Country using ${path0}/results/1-appendix_a/Supplementary_Table_A7.tex, replace c(freq col cum) f(0c 2c 2c) clab(Frequency Percent Cumulative) sort bt style(tex)
}
display "Table A7: Country of Global Ultimate Ownership"
tab Country, sort
end

********************************************************************************
*Table A8
*Sectoral and Country Composition for all MNCs and procomer buyers
********************************************************************************
prog descevent_sectcomp_proco
display "table A8 Sectoral and Country Composition for all MNCs and procomer buyers"
*mncs main sample
quiet{
estimates clear 
use $analysis_data, clear
*firms in main sample 
reghdfe y D* if past!=1, absorb(year#sector4#province IDs) resid(temp_res)
bys ID: egen res=mean(temp_res)
keep if res!=.

*merging proco 
preserve
	use raw_data/Procomer_allmatchedpairs.dta, clear
	bys cedulabuyer: gen dup=cond(_N==1,0,_n)
	drop if dup>1
	keep cedulabuyer
	rename cedula buyer 
	tempfile proco_buyers 
	save `proco_buyers', replace
restore
merge m:1 buyer using `proco_buyers'
gen proco_buyer=(_m==3)
drop _m 
drop if buyer==""

*merging with countries 
merge m:1 buyer using $mnc_countries
keep if _m==3
replace country="US" if country=="PR"

*continents
global usa_can "US CA"
global europe "ES CH GB DE SV FR IT LU NL DK IE BE SE NO AT HU RS GR BG"
global asia_aus "CN JP ID KR SG HK IN AU"
global latin "PA MX CO NI CL VE GT BR PE CW HN TT BZ EC BM KY"
gen continent=""

foreach v of global usa_can {
replace continent="USA-Canada" if country=="`v'"
} 
foreach v of global europe {
replace continent="Europe" if country=="`v'"
}
foreach v of global asia_aus {
replace continent="Asia-Aust" if country=="`v'"
}
foreach v of global latin {
replace continent="Latinoamerica" if country=="`v'"
}
encode continent, gen(cont)

** Distribution by sector and country of MNC
preserve
collapse (mean) event, by (buyer b_sector_g continent)
estpost tab b_sector_g , nototal
est store mnc 
local lab_sect `e(labels)'
estpost tab cont , nototal
local lab_cont `e(labels)'
est store cont
restore 

** Distribution by sector and country of proco MNC
preserve
keep if proco_buyer==1
collapse (mean) event, by (buyer b_sector_g continent)
estpost tab b_sector_g , nototal
est store mnc_proco 
estpost tab cont , nototal
est store cont_proco
restore 
}

esttab mnc mnc_proco using ${path0}/results/1-appendix_a/Supplementary_Table_A8.tex, replace label varlabels(`lab_sect') cells(pct(fmt(2) label(" "))) mtitles("All MNCs" "Procomer MNCs") noobs nonumber type

esttab cont cont_proco using ${path0}/results/1-appendix_a/Supplementary_Table_A8.tex, append label varlabels(`lab_sect') cells(pct(fmt(2) label(" "))) mtitles("All MNCs" "Procomer MNCs") noobs nonumber type

end

********************************************************************************
*data preparation for Tables A2 and A5
********************************************************************************
{
prog data_prep
quiet{
*balance sheet data
* same chunk as in 1-event_data_creation.do
		balance_sheet

*no CONSTRUCTION/Real estate or EDUCATION or FINANCIAL
drop if sector_g==5 & foreign_list!=1
drop if sector_g==10 & foreign_list!=1
drop if sector_g==13 & foreign_list!=1
drop if sector_g==17 & foreign_list!=1
* NO GOVERNMENT, NO NGO
drop if inolucrogrupo==1 | ipubgrupo==1
* NO HOUSEHOLDS
replace sector_institucional=substr(sector_institucional, 1, 3)
keep if sector_ins=="S11" 

}

end

********************************************************************************
*balance sheet data
* same chunk as in 1-event_data_creation.do
********************************************************************************
prog balance_sheet
display "balance_sheet"

use $revec_group, clear
compress
keep ID sophistication corp empresarial estado_* sector_institucional ///
 ipubgrupo inolucrogrupo nombre_r provincia firm_AE firm_ciiu4 ingresosir_* ///
 trabaj_* salarios_* va_* exports_* imports_* entry exit sector_g ZF* ///
 foreign_l* total_activo_neto_* activos_fijos_* costo_de_ventas_* 

**  CONVERTING TO REAL VALUES 
* money in millions
local money_col "ingresosir va costo_de_ventas total_activo_neto activos_fijos"
forvalue year =2005/2017{
foreach vari of local money_col{
replace `vari'_`year'= (100*(`vari'_`year')/${ipc`year'})/1000000 
format `vari'_`year' %16.0g
}
}
* money in Th
local money_col "salarios"
forvalue year =2005/2017{
foreach vari of local money_col{
replace `vari'_`year'= (100*abs(`vari'_`year')/${ipc`year'})/1000
format `vari'_`year' %16.0g
}
}

** IMPORTS AND EXPORTS ARE IN DOLLARS (we convert them to colones)
local money_dol "exports imports"
forvalue year =2005/2017{
foreach vari of local money_dol{
replace `vari'_`year'= ${tc_`year'}*(100*abs(`vari'_`year')/${ipc`year'})/1000000
format `vari'_`year' %16.0g
}
}

** RESHAPING
local resh_var "ingresosir_ costo_de_ventas_ trabaj_ estado_ salarios_ va_ exports_ imports_ ZF_broad_ total_activo_neto_ activos_fijos_"
reshape long `resh_var', i(ID) j(year)
compress

* we keep data for the same period as in the transactions dataset for consistency
drop if year<2008
drop if year==2018
destring firm_ciiu4, gen(sector4)
gen sector3=floor(sector4/10)
gen sector2=floor(sector3/10)

*****************************

*IN ANALYSIS DATA
preserve
use $analysis_data, clear
keep ID year
tempfile analysis
save `analysis', replace
restore
merge 1:1 year ID using `analysis'
gen in_main_sample=(_m==3)
drop _m 
*huge outliers in 2017
replace total_activo_neto_=. if ID=="3101217102" & year==2017
replace total_activo_neto_=. if ID=="3101018738" & year==2017
*renaming
rename ingresosir sales
rename trabaj_ workers
rename salarios wage_bill
rename va_ value_added 
rename costo_de_ventas input_costs
rename total_activo_neto total_net_assets

global vars "sales exports_ imports_ value_added input_costs total_net_assets"
*first from mill colones to th of USD (tc=500)
foreach v of global vars {
replace `v'=`v'*1000/500
}
*wage bill in thousands already (need to convert to USD) 
replace wage_bill=wage_bill/500
gen entered_bef_2005=100*(entry<=2005)

************************************************

*** only keep firm-year pairs with both workers and sales 
drop if sales==. | workers==.

* WITHOUT SECTOR
drop if sector_g==.


end

}

********************************************************************************
*Tables D1 and D3,  Tables A8 and A9, Figures A3, A4, A5
*(part b of D1 is in the main text code with the matching estimators
*Alternative Empirical Strategies: ``Productive Linkages" 
********************************************************************************
{
prog procomer

*SL INFO
	quiet sl_info

*firm characteristics
	quiet characteristics

*transactions
	quiet transactions
	
*merging previous datasets
	quiet merging_procomer
	
*Figures A3, A4, and A5: size of sl and diff in scores
display "Figures A3, A4, and A5"
	quiet size_scores

*Tables A8 and Table A9: table of descriptive statistics
display "Tables A8 and A9"
	descriptives_procomer
end

***************
*program: sl_info
***************
prog sl_info
display "program: sl_info"
quiet{
use $SL_info, clear

preserve
gen minus_winner=-winner
sort ID minus_winner, stable
by ID: gen dup=cond(_N==1,0,_n)
drop if dup>1
replace MNC0="" if winner!=1
keep ID event MNC0 winner monto
save temp/sl.dta, replace
restore

collapse event, by(buyer)
drop event
tostring buyer, replace
save temp/buyers.dta, replace
}
end

*************************
*program: firm characteristics
*************************
prog characteristics
display "program: firm characteristics"
quiet{
*using balance sheet data
use $revec_group, clear
keep ID sophistication corp empresarial estado_* sector_institucional ///
ipubgrupo inolucrogrupo nombre_r provincia firm_AE firm_ciiu4 ingresosir_* ///
trabaj_* salarios_* va_* exports_* imports_* entry exit sector_g ZF* ///
 total_activo_neto_* activos_fijos_* costo_de_ventas_* ventas_* trab_calif_*
merge 1:1 ID using temp/sl.dta

*  CONVERTING TO REAL VALUES  million dollars
local money_col "ingresosir va costo_de_ventas total_activo_neto activos_fijos ventas"
forvalue year =2005/2017{
foreach vari of local money_col{
replace `vari'_`year'= (100*(`vari'_`year')/${ipc`year'})/1000000/${tc_`year'} 
format `vari'_`year' %16.0g
}
}
* salaries in th dollars
local money_col "salarios"
forvalue year =2005/2017{
foreach vari of local money_col{
replace `vari'_`year'= (100*abs(`vari'_`year')/${ipc`year'})/1000/${tc_`year'} 
format `vari'_`year' %16.0g
}
}
* IMPORTS AND EXPORTS ARE IN DOLLARS 
local money_dol "exports imports"
forvalue year =2005/2017{
foreach vari of local money_dol{
replace `vari'_`year'= (100*abs(`vari'_`year')/${ipc`year'})/1000000
format `vari'_`year' %16.0g
}
}
* RESHAPING
local resh_var "ingresosir_ costo_de_ventas_ trabaj_ estado_ salarios_ va_ exports_ imports_ ZF_broad_ total_activo_neto_ activos_fijos_ ventas_ trab_calif_"
reshape long `resh_var', i(ID) j(year)

compress
drop if ingresos==. & trabaj==.

* we keep data for the same period as in the transactions dataset for consistency
drop if year<2008
drop if year>=2018
rename ID seller

*saving data
save temp/sellers0.dta, replace

drop if _m==1
drop _m
compress

destring firm_ciiu4, gen(sector4)
gen sector3=floor(sector4/10)
gen sector2=floor(sector3/10)

*saving data
save temp/sellers.dta, replace
}
end

*******************
*program: transactions
*******************
prog transactions
display "program: transactions"
quiet{
use $transactions, clear
drop if pot==1
drop pot
merge m:1 seller year using temp/sellers
bys seller: egen max_m=max(_m)
keep if max_m==3

* TRANSACTIONS TO REAL VALUE
local trans_vari "monto_tot"
foreach v of local trans_vari{
forvalue i=2008/2017{
replace `v'=(100*(`v')/${ipc`i'})/1000000 if year==`i'
}
}
local trans_vari "trans_"
foreach v of local trans_vari{
forvalue i=2008/2017{
replace `v'=(100*(`v')/${ipc`i'})/1000000/${tc_`i'} if year==`i'
}
}
drop _m max_m
merge m:1 buyer using temp/buyers

gen mnc_buyer=(_m==3)
gen trigg=(MNC0==buyer & MNC0!="")

bys seller year: egen max_t=max(trigg)
replace max_t=. if year!=event 

gen minus_trans=-trans
sort seller year minus_trans , stable 
by seller year: gen pos=_n
replace trans=monto if pos==1 & event==year &  max_t==0
replace trigg=1 if pos==1 & event==year  & max_t==0
drop max_t pos

sort seller trigg year , stable 
by seller trigg: gen spell=_n if trigg!=0
by seller trigg: gen spelltot=_N if trigg!=0
gen eventyear=spell-1

drop if _m==2
drop _m

*saving data
save temp/trans.dta , replace

* TRANS TO NON-TRIGGERING MNC
 
gen double trans2=trans*trigg
bys seller year: egen double tot_trans_trig=total(trans2)
drop trans2
bys seller year: egen double all_trans=total(trans)
gen n_clients=1
gen n_trig=trigg
compress

collapse (sum) n_clients n_trig (mean) event all_trans tot_trans_trig ingres, by(year seller)

rename seller ID
save temp/fore_trans, replace
}
end

******************************
*program: merging previous datasets
******************************
prog merging_procomer
display "transactions"
use $SL_info, clear
rename ID seller
joinby seller using temp/sellers

rename seller ID
merge m:1 ID year using temp/fore_trans
drop if _m==2
drop _m
gsort case -winner_d ID year
order case winner_d ID year, first
save temp/merged.dta, replace
end

******************************
*program: size of sl and diff in scores
******************************
prog size_scores
use temp/merged.dta, clear

preserve
quiet{
egen tag_win=tag(ID case) if winner_d==1
egen tag_loser= tag(ID case) if winner_d==0 
bys case: egen sizeSL=total(tag_l)
bys case: egen wins=total(tag_win)

collapse sizeSL, by(case)
tab sizeSL
capture drop tag_loser
egen sizeSL_numrela=tag(case)
replace sizeSL_numrela=sizeSL if sizeSL_numrela!=0
replace sizeSL_numrela=. if sizeSL_numrela==0
tab sizeSL_numrela, matcell(x)
local namex ""SL1""
summ sizeSL_numrela
local max_s=`r(max)'
forvalue i=2/`r(max)'{
local namex "`namex' "SL`i'""
}
mat rownames x=`namex'
}
mat list x
restore

* SCORES
preserve
collapse diag winner, by(ID case)
twoway   (histogram diag  if winner_d==0 ,  frac start(50)  width(5)  lcolor(gs11) fcolor(gs11)  ) (histogram diag  if winner_d==1 , frac start(50) width(5) lcolor(black) fcolor(none)  ), legend(order(1 "Losers"  2 "Winners")) graphregion(fcolor(white))   ytitle("Fraction" , size(large)) xtitle("Procomer score", size(large)) xlabel(  , labs(large) valuelabel angle(0)) ylabel( , labs(large)  )
graph export "${path0}/results/1-appendix_a/Supplementary_Figure_A2a.eps", as(eps) replace
restore

preserve
collapse diag, by(case winner)
gen temp_winner=diag if winner==1
gen temp_loser=diag if winner==0
bys case: egen wins=mean(temp_w)
bys case: egen los=mean(temp_l)
gen diff=wins-los
collapse diff, by (case)
twoway   (histogram diff ,  frac lcolor(none) fcolor(gs13)  ), legend() graphregion(fcolor(white))   ytitle("Fraction", size(large)) xtitle("Difference in Procomer score: winners - losers",  size(large) )  xlabel(  , labs(large) valuelabel angle(0)) ylabel( , labs(large)  )
graph export "${path0}/results/1-appendix_a/Supplementary_Figure_A2b.eps", as(eps) replace
restore

*scores vs va per worker
use temp/merged.dta, clear
keep if year==event-1
gen va_w=log(1000*va_/trabaj)
separate va_w, by(winner) veryshortlabel
*labels 
local labels "1 "3" 2 "7" 3 "20" 4 "50" 5 "140""

twoway (scatter va_w? diag  ,  msymbol(oh dh) mlcolor(gray blue) msize(*2 ..) ) || (lfit va_w diag , lwidth(thick) lcolor(maroon) lpattern(shortdash)), ///
legend(order(1 "Losers"  2 "Winners") pos(5) ring(0) col(1)) graphregion(fcolor(white))   ytitle("VA per Worker (Th USD)", size(large)) xtitle("Procomer Score", ///
  size(large) )  xlabel(  , labs(large) valuelabel angle(0))  yla(`labels', labs(large) ang(h))  
graph export "${path0}/results/1-appendix_a/Supplementary_Figure_A3.eps", as(eps) replace 
end

********************************************************************************
*program: table of descriptive statistics
********************************************************************************
prog descriptives_procomer
display "table of descriptive statistics"
*descriptive stats table 
quiet{
use temp/merged.dta, clear
 
replace imports=0 if imports==.
replace exports=0 if exports==.
gen runner_up=1-winner_d
local variab "all_trans  va_  n_clients"
foreach v of local variab{
replace `v'=0 if `v'==.
if "`v'"!="n_clients"{
gen per_w_`v'=1000*`v'/trabaj
}
if "`v'"=="n_clients"{
gen per_w_`v'=`v'/trabaj
}
}
foreach v of local variab{
gen perc_`v'= per_w_`v' if year==event-1
bys ID case: egen temp_`v'=mean(perc_`v')
drop perc_`v'
gen perc_`v'= per_w_`v' if year==event
bys ID case: egen temp2_`v'=mean(perc_`v')
drop perc_`v'
gen perc_`v'= per_w_`v' if year==event+2
bys ID case: egen temp3_`v'=mean(perc_`v')
drop perc_`v'
gen perc_`v'= per_w_`v' if year==event-2
bys ID case: egen temp4_`v'=mean(perc_`v')
drop perc_`v'
gen perc_w_`v'=temp_`v'
replace perc_w_`v'=temp2_`v' if perc_w_`v'==.
replace perc_w_`v'=temp3_`v' if perc_w_`v'==.
replace perc_w_`v'=temp4_`v' if perc_w_`v'==.
drop temp*
}

collapse `variab' perc_* trabaj diag runner_up winner_d, by(case ID)

eststo clear

if $BCCR == 1 {
myttests trabaj perc_w_va perc_w_all perc_w_n diag, by(runner_up) 
}
scalar n_treat=r(N_1)
scalar n_control=r(N_2)
estadd scalar n_treat
estadd scalar n_control

label variable trabaj_ "Number of Workers"
label variable perc_w_va_ "Value-added  per worker"
label variable perc_w_all_trans "Total transactions per worker"
label variable perc_w_n_clients "Number of buyers per worker"
label variable diag "Procomer score"

esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A9.tex, label nomtitle nonumbers  ///
    cells("mu_1(fmt(a3)) mu_2 d(star pvalue(d_p))" ///
	"mu_1_se(par fmt(2)) mu_2_se(par fmt(2)) d_se(par fmt(2))" ) ///
	collabels("Winners" "Losers" "Difference") ///
	stats(n_treat  n_control , fmt(0 0) labels("\# Winners" "\# Losers" )) ///
	replace type
}
esttab , nomtitle nonumbers  ///
    cells("mu_1(fmt(a3)) mu_2 d(star pvalue(d_p))" ///
	"mu_1_se(par fmt(2)) mu_2_se(par fmt(2)) d_se(par fmt(2))" ) ///
	stats(n_treat  n_control , fmt(0 0) labels("\# Winners" "\# Losers" )) ///
	replace 
	
quiet{

* TABLE
use temp/trans.dta, clear

* LENGTH OF THE RELATIONSHIP WITH THE TRIGGERING MNC
sort seller year

* TRANSACTIONS IN TH USD
local trans_vari "trans monto"
foreach v of local trans_vari{
forvalue i=2008/2017{
replace `v'=`v'*1000 if year==`i'
}
}
keep if trigg==1 & winn==1
gen trans_first_mnc=trans if event==year & trigg==1

collapse (sum) trigg  trans_first_mnc, by (seller)
}

label var trans_first_mnc "First transaction with MNC (thousands of US\\$)"
estpost tabstat trans_first_mnc, stat(count mean p50 sd)
esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A10.tex, replace label ///
cells("count(label(\textit{N}) fmt(0)) mean(label(Mean) fmt(2)) p50(label(Median) fmt(2) ) sd(label(S.D.) fmt(2))") ///
nonum noobs f booktabs

label var trigg "Length of relationship with triggering MNCs"
estpost tabstat trigg, stat(count mean p50 sd)
esttab using ${path0}/results/1-appendix_a/Supplementary_Table_A10.tex, append label ///
 cells("count(fmt(0)) mean(fmt(2)) p50(fmt(2) ) sd(fmt(2))") ///
 nonum noobs collabels(none) f plain booktabs nogaps
end
}


********************************************************************************
* EXCHANGE RATE AND PRICES 
********************************************************************************
quiet{
************************************************************ 
***  PRODUCER PRICE INDEX  (yearly average) 2013 ==100    **  
************************************************************  	
global ipc2005 = 50.99*100/92.88
global ipc2006 = 57.96*100/92.88
global ipc2007 = 63.38*100/92.88
global ipc2008 = 75.43*100/92.88
global ipc2009 = 77.53*100/92.88
global ipc2010 = 81.15*100/92.88
global ipc2011 = 85.57*100/92.88
global ipc2012 = 89.78*100/92.88
global ipc2013 = 92.88*100/92.88
global ipc2014 = 98.99*100/92.88
global ipc2015 = 98.93*100/92.88
global ipc2016 = 100.08*100/92.88
global ipc2017 = 100.77*100/92.88

********************************* 
***  AVERAGE EXCHANGE RATE    ***  
*********************************
* 1 March of each year: average compra-venta
global tc_2005=477.76578
global tc_2006=511.29055
global tc_2007=516.59063
global tc_2008=526.35683
global tc_2009=573.35611
global tc_2010=525.68364
global tc_2011=505.69
global tc_2012=502.8868
global tc_2013=499.75405
global tc_2014=538.36123
global tc_2015=534.55408
global tc_2016=544.76658
global tc_2017=567.55509
}

********************************************************************************
* PROGRAM TO GENERATE DIFF IN MEANS
********************************************************************************
capture program drop myttests
program myttests, eclass
     syntax varlist [if] [in], by(varname) [ * ]
     marksample touse
     markout `touse' `by'
	 local varia_program "mu_1 mu_2 n_1 n_2 mu_1_se mu_2_se d d_se d_t d_p"
     tempname `varia_program'
      foreach var of local varlist {
         qui ttest `var' if `touse', by(`by') `options'
         mat `mu_1' = nullmat(`mu_1'), r(mu_1)
         mat `mu_2' = nullmat(`mu_2'), r(mu_2)
         mat `n_1' = nullmat(`n_1'), r(N_1)
         mat `n_2' = nullmat(`n_2'), r(N_2)		 
         mat `mu_1_se' = nullmat(`mu_1_se'), r(sd_1)
         mat `mu_2_se' = nullmat(`mu_2_se'), r(sd_2)		 
         mat `d'    = nullmat(`d'   ), r(mu_1)-r(mu_2)
         mat `d_se' = nullmat(`d_se'), r(se)
         mat `d_t'  = nullmat(`d_t' ), r(t)
         mat `d_p'  = nullmat(`d_p' ), r(p)
     }
         foreach mat in `varia_program' {
         mat coln ``mat'' = `varlist'
     }
     tempname b V
     mat `b' = `mu_1'*0
     mat `V' = `b''*`b'
     eret post `b' `V'
     eret local cmd "myttests"
     foreach mat in `varia_program' {
        eret mat `mat' = ``mat''
    }
 end

********************************* 
***  AVERAGE EXCHANGE RATE    ***  
*********************************
*average compra-venta
global tc_2005=477.76578
global tc_2006=511.29055
global tc_2007=516.59063
global tc_2008=526.35683
global tc_2009=573.35611
global tc_2010=525.68364
global tc_2011=505.69
global tc_2012=502.8868
global tc_2013=499.75405
global tc_2014=538.36123
global tc_2015=534.55408
global tc_2016=544.76658
global tc_2017=567.55509
 
******************************************************************************** 
***  EXECUTE THE PROGRAM main   
********************************************************************************
main

******************************************************************************** 
***  TIMER
********************************************************************************
timer off 1
*time in seconds
timer list 1
*time in minutes
local time_ =round(`r(t1)'/60)
display "the code takes `time_' minutes in total"

log close
translate `logfile'.smcl `logfile'.txt , replace linesize(250)
capture erase `logfile'.smcl
